██████╗ ██████╗  █████╗ ████████╗ ██████╗  ██████╗ ██╗     ███████╗

██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║     ██╔════╝

██║  ██║██████╔╝███████║   ██║   ██║   ██║██║   ██║██║     ███████╗

██║  ██║██╔══██╗██╔══██║   ██║   ██║   ██║██║   ██║██║     ╚════██║

██████╔╝██████╔╝██║  ██║   ██║   ╚██████╔╝╚██████╔╝███████╗███████║

╚═════╝ ╚═════╝ ╚═╝  ╚═╝   ╚═╝    ╚═════╝  ╚═════╝ ╚══════╝╚══════╝

██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ██╗ ██╗ ██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗██║ ██║ ██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║███████║ ██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║╚════██║ ██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝ ██║ ╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝ </pre>

Recipe #04 - Let's cook!

Deserts:

- Deploy sp_whoIsActive across instances

NOTE: I wrote about it in my blog post: New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute

Set variables



In [ ]:

    
$dbatools1 = "localhost,1433"
$dbatools2 = "localhost,14333"
$secureString = ConvertTo-SecureString "dbatools.IO" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "sqladmin", $secureString

$databaseToDeploy = "master"

Deploy the stored procedure on a list of instances



In [ ]:

    
# If you have internet access
Install-DbaWhoIsActive -SqlInstance $dbatools1, $dbatools2 -SqlCredential $cred -Database $databaseToDeploy -Confirm:$false

If you have a file version of it



In [ ]:

    
$deploySplat = @{
    SqlInstance = $dbatools1, $dbatools2
    SqlCredential = $cred
    Database = $databaseToDeploy
    LocalFile = "D:\Presentations\PASS Marathon Portuguese 2020\Deploy-spWhoIsActive\who_is_active_v11_32.sql" 
    #Confirm = $false
}
# Without internet access but with a version saved on a local file
Install-DbaWhoIsActive @deploySplat

Execute to show that it is there



In [ ]:

    
Invoke-DbaQuery -SqlInstance $dbatools1 -SqlCredential $cred -Query "exec sp_whoIsActive @show_sleeping_spids = 2" -Database $databaseToDeploy

There is also a command to invoke the stored procedure



In [ ]:

    
Invoke-DbaWhoIsActive -SqlInstance $dbatools1 -SqlCredential $cred -ShowSleepingSpids 2

Example by getting the list of instances from your central server



In [ ]:

    
$SQLServers = Invoke-DbaQuery -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database $databaseToDeploy